package com.lelouch.bestpractice.mongorepository.tools;

import com.google.common.collect.Lists;
import com.lelouch.bestpractice.mongorepository.domain.WetlandBasicEntity;
import com.lelouch.bestpractice.mongorepository.domain.WetlandTypeInfo;
import com.lelouch.bestpractice.mongorepository.exception.ExcelUploadException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * excel表格上传工具
 *
 * @author lcy
 * @create 2018-09-15 08:48
 */
@Slf4j
public class UploadExcelUtil {

    private final static String XLS = "xls";
    private final static String XLSX = "xlsx";
    private final static Double DOUBLE_ZERO = 0.0;
    private final static Integer DEFALUT_SCALE = 12;
    private final static SimpleDateFormat SIMPLE_DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd");
    private final static String DEFALUT_YEAR = "1970";
    private final static String DEFALUT_MONTH = "1";
    private final static String DEFALUT_DAY = "1";

    //public static void main(String[] args) throws IOException {
    //    assembleWetlandBasicEntityListFormExcel(null);
    //}


    public static List<WetlandBasicEntity> assembleWetlandBasicEntityListFormExcel(MultipartFile uploadExcel) throws IOException {
        checkFile(uploadExcel);
        List<WetlandBasicEntity> wetlandBasicEntityList = Lists.newArrayList();
        Workbook workbook = getWorkBook(uploadExcel);
        // 获得当前工作表
        Sheet sheet = workbook.getSheetAt(0);
        //获取表的宽度
        int columnWidth = sheet.getRow(0).getPhysicalNumberOfCells();
        log.info("excel表的宽度为:{}", columnWidth);
        if (columnWidth != 57) {
            log.error("excel表的列数不为57");
            throw new ExcelUploadException("excel表的列数不为57");
        }
        int firstRow = sheet.getFirstRowNum();
        int lastRow = sheet.getLastRowNum();
        log.info("首行行数{}", firstRow);
        log.info("末行行数{}", lastRow);

        StringBuilder dataExceptionMessage = new StringBuilder("");
        for (int i = firstRow + 1; i < lastRow + 1; i++) {
            Row row = sheet.getRow(i);
            if (StringUtils.isBlank(getCellValue(row.getCell(0)))) {
                dataExceptionMessage.append("第").append(i + 1).append("行第").append(1).append("列数据不可以为空!\n");
            }
            if (StringUtils.isBlank(getCellValue(row.getCell(4)))) {
                dataExceptionMessage.append("第").append(i + 1).append("行第").append(5).append("列数据不可以为空!\n");
            }
            if (StringUtils.isBlank(getCellValue(row.getCell(5)))) {
                dataExceptionMessage.append("第").append(i + 1).append("行第").append(6).append("列数据不可以为空!\n");
            }
            if (StringUtils.isBlank(getCellValue(row.getCell(6)))) {
                dataExceptionMessage.append("第").append(i + 1).append("行第").append(7).append("列数据不可以为空!\n");
            }
            if (StringUtils.isBlank(getCellValue(row.getCell(14)))) {
                dataExceptionMessage.append("第").append(i + 1).append("行第").append(15).append("列数据不可以为空!\n");
            }
            if (StringUtils.isBlank(getCellValue(row.getCell(15)))) {
                dataExceptionMessage.append("第").append(i + 1).append("行第").append(16).append("列数据不可以为空!\n");
            }
        }
        if (!StringUtils.isBlank(dataExceptionMessage.toString())) {
            log.error(dataExceptionMessage.toString());
            throw new ExcelUploadException(dataExceptionMessage.toString());
        }


        for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            String c0 = getCellValue(row.getCell(0));
            String c1 = getCellValue(row.getCell(1));
            String c2 = getCellValue(row.getCell(2));
            String c3 = getCellValue(row.getCell(3));
            String c4 = getCellValue(row.getCell(4));
            String c5 = getCellValue(row.getCell(5));
            String c6 = getCellValue(row.getCell(6));
            String c7 = getCellValue(row.getCell(7));
            String c8 = getCellValue(row.getCell(8));
            String c9 = getCellValue(row.getCell(9));
            String c10 = getCellValue(row.getCell(10));
            String c11 = getCellValue(row.getCell(11));
            String c12 = getCellValue(row.getCell(12));
            String c13 = getCellValue(row.getCell(13));
            String c14 = getCellValue(row.getCell(14));
            String c15 = getCellValue(row.getCell(15));
            String c16 = getCellValue(row.getCell(16));
            String c17 = getCellValue(row.getCell(17));
            String c18 = getCellValue(row.getCell(18));
            String c19 = getCellValue(row.getCell(19));
            String c20 = getCellValue(row.getCell(20));
            String c21 = getCellValue(row.getCell(21));
            String c22 = getCellValue(row.getCell(22));
            String c23 = getCellValue(row.getCell(23));
            String c24 = getCellValue(row.getCell(24));
            String c25 = getCellValue(row.getCell(25));
            String c26 = getCellValue(row.getCell(26));
            String c27 = getCellValue(row.getCell(27));
            String c28 = getCellValue(row.getCell(28));
            String c29 = getCellValue(row.getCell(29));
            String c30 = getCellValue(row.getCell(30));
            String c31 = getCellValue(row.getCell(31));
            String c32 = getCellValue(row.getCell(32));
            String c33 = getCellValue(row.getCell(33));
            String c34 = getCellValue(row.getCell(34));
            String c35 = getCellValue(row.getCell(35));
            String c36 = getCellValue(row.getCell(36));
            String c37 = getCellValue(row.getCell(37));
            String c38 = getCellValue(row.getCell(38));
            String c39 = getCellValue(row.getCell(39));
            String c40 = getCellValue(row.getCell(40));
            String c41 = getCellValue(row.getCell(41));
            String c42 = getCellValue(row.getCell(42));
            String c43 = getCellValue(row.getCell(43));
            String c44 = getCellValue(row.getCell(44));
            String c45 = getCellValue(row.getCell(45));
            String c46 = getCellValue(row.getCell(46));
            String c47 = getCellValue(row.getCell(47));
            String c48 = getCellValue(row.getCell(48));
            String c49 = getCellValue(row.getCell(49));
            String c50 = getCellValue(row.getCell(50));
            String c51 = getCellValue(row.getCell(51));
            String c52 = getCellValue(row.getCell(52));
            String c53 = getCellValue(row.getCell(53));
            String c54 = getCellValue(row.getCell(54));
            String c55 = getCellValue(row.getCell(55));
            String c56 = getCellValue(row.getCell(56));


            WetlandBasicEntity wbe = new WetlandBasicEntity();
            wbe.setName(c0);
            wbe.setWlin_gis_mapserver(c1);
            wbe.setWlin_location_gis_mapserver(c1);
            wbe.setWlin_sectorization_mapserver(c1);
            wbe.setWlin_soil_gis_mapserver(c1);
            wbe.setWlin_type_gis_mapserver(c1);
            if (StringUtils.isBlank(c2))
                wbe.setWlin_latitude(DOUBLE_ZERO);
            else
                wbe.setWlin_latitude(Coordinate2Decimal.C2D(c2));
            if (c3.equals(""))
                wbe.setWlin_longitude(DOUBLE_ZERO);
            else
                wbe.setWlin_longitude(Coordinate2Decimal.C2D(c3));
            wbe.setProvince(c4);
            wbe.setCity(c5);
            wbe.setDistrict(c6);
            if (StringUtils.isBlank(c7))
                wbe.setWlin_scale(DEFALUT_SCALE);
            else
                wbe.setWlin_scale(Integer.parseInt(c7));
            wbe.setWlin_overview(c8);
            if (StringUtils.isBlank(c9)) {
                wbe.setWlin_official_area("0");
            } else {
                wbe.setWlin_official_area(c9);
            }
            if (StringUtils.isBlank(c10)) {
                wbe.setWlin_gis_area("0");
            } else {
                wbe.setWlin_gis_area(c10);
            }


            wbe.setWlin_location_gis_layer(c11);
            wbe.setWlin_location_des(c12);
            wbe.setWlin_border_des(c13);
            wbe.setLevel(c14);
            wbe.setForm(c15);
            wbe.setWlin_protection_object(c16);
            if (StringUtils.isBlank(c17)) {
                c17 = DEFALUT_YEAR;
            }
            if (StringUtils.isBlank(c18)) {
                c18 = DEFALUT_MONTH;
            }
            if (StringUtils.isBlank(c19)) {
                c19 = DEFALUT_DAY;
            }


            Date date = null;
            try {
                date = SIMPLE_DATE_FORMAT.parse(Integer.parseInt(c17) + "-" + Integer.parseInt(c18) + "-" + Integer.parseInt(c19));
            } catch (ParseException e) {
                e.printStackTrace();
            }
            wbe.setWlin_bulid_date(date);


            wbe.setWlin_sectorization_layer(c20);
            wbe.setWlin_type_gis_layer(c21);

            ArrayList<WetlandTypeInfo> typeInfos = new ArrayList<WetlandTypeInfo>();
            typeInfos.add(0, new WetlandTypeInfo("qhsy", 0, 0, "jhyhasd"));
            typeInfos.add(1, new WetlandTypeInfo("shj", 0, 0, "jhyhasd"));
            typeInfos.add(2, new WetlandTypeInfo("ysha", 0, 0, "jhyhasd"));
            typeInfos.add(3, new WetlandTypeInfo("ssht", 0, 0, "jhyhasd"));
            typeInfos.add(4, new WetlandTypeInfo("ynzht", 0, 0, "jhyhasd"));
            typeInfos.add(5, new WetlandTypeInfo("hsl", 0, 0, "jhyhasd"));
            typeInfos.add(6, new WetlandTypeInfo("hksy", 0, 0, "jhyhasd"));
            typeInfos.add(7, new WetlandTypeInfo("sjzszsd", 0, 0, "jhyhasd"));
            typeInfos.add(8, new WetlandTypeInfo("haxxsh", 0, 0, "jhyhasd"));
            typeInfos.add(9, new WetlandTypeInfo("haxdsh", 0, 0, "jhyhasd"));
            typeInfos.add(10, new WetlandTypeInfo("cxssc", 0, 0, "jhyhasd"));
            typeInfos.add(11, new WetlandTypeInfo("cjyszz", 0, 0, "jhyhasd"));

            typeInfos.add(12, new WetlandTypeInfo("yjxhl", 0, 0, "hlsd"));
            typeInfos.add(13, new WetlandTypeInfo("jjxhjxxhl", 0, 0, "hlsd"));
            typeInfos.add(14, new WetlandTypeInfo("hfpysd", 0, 0, "hlsd"));
            typeInfos.add(15, new WetlandTypeInfo("kstrdsd", 0, 0, "hlsd"));

            typeInfos.add(16, new WetlandTypeInfo("yjxdsh", 0, 0, "hpsd"));
            typeInfos.add(17, new WetlandTypeInfo("yjxxsh", 0, 0, "hpsd"));
            typeInfos.add(18, new WetlandTypeInfo("jjxdsh", 0, 0, "hpsd"));
            typeInfos.add(19, new WetlandTypeInfo("jjxxsh", 0, 0, "hpsd"));

            typeInfos.add(20, new WetlandTypeInfo("xlzz", 0, 0, "zzsd"));
            typeInfos.add(21, new WetlandTypeInfo("cbzz", 0, 0, "zzsd"));
            typeInfos.add(22, new WetlandTypeInfo("gczz", 0, 0, "zzsd"));
            typeInfos.add(23, new WetlandTypeInfo("slzz", 0, 0, "zzsd"));
            typeInfos.add(24, new WetlandTypeInfo("nlyz", 0, 0, "zzsd"));
            typeInfos.add(25, new WetlandTypeInfo("jjxxszz", 0, 0, "zzsd"));
            typeInfos.add(26, new WetlandTypeInfo("zzhcd", 0, 0, "zzsd"));
            typeInfos.add(27, new WetlandTypeInfo("drsd", 0, 0, "zzsd"));
            typeInfos.add(28, new WetlandTypeInfo("dsqlzsd", 0, 0, "zzsd"));

            typeInfos.add(29, new WetlandTypeInfo("kt", 0, 0, "rgsd"));
            typeInfos.add(30, new WetlandTypeInfo("yhssh", 0, 0, "rgsd"));
            typeInfos.add(31, new WetlandTypeInfo("scyzc", 0, 0, "rgsd"));
            typeInfos.add(32, new WetlandTypeInfo("dtdst", 0, 0, "rgsd"));
            typeInfos.add(33, new WetlandTypeInfo("yt", 0, 0, "rgsd"));

            if (!StringUtils.isBlank(c22))
                typeInfos.set(0, new WetlandTypeInfo("qhsy", Double.valueOf(c23.split(" ")[0]), Double.valueOf(c23.split(" ")[1].trim()), "jhyhasd"));
            if (!StringUtils.isBlank(c23))
                typeInfos.set(1, new WetlandTypeInfo("shj", Double.valueOf(c24.split(" ")[0]), Double.valueOf(c24.split(" ")[1].trim()), "jhyhasd"));
            if (!StringUtils.isBlank(c24))
                typeInfos.set(2, new WetlandTypeInfo("ysha", Double.valueOf(c25.split(" ")[0]), Double.valueOf(c25.split(" ")[1].trim()), "jhyhasd"));
            if (!StringUtils.isBlank(c25))
                typeInfos.set(3, new WetlandTypeInfo("ssht", Double.valueOf(c26.split(" ")[0]), Double.valueOf(c26.split(" ")[1].trim()), "jhyhasd"));
            if (!StringUtils.isBlank(c26))
                typeInfos.set(4, new WetlandTypeInfo("ynzht", Double.valueOf(c27.split(" ")[0]), Double.valueOf(c27.split(" ")[1].trim()), "jhyhasd"));
            if (!StringUtils.isBlank(c27))
                typeInfos.set(5, new WetlandTypeInfo("hsl", Double.valueOf(c28.split(" ")[0]), Double.valueOf(c28.split(" ")[1].trim()), "jhyhasd"));
            if (!StringUtils.isBlank(c28))
                typeInfos.set(6, new WetlandTypeInfo("hksy", Double.valueOf(c29.split(" ")[0]), Double.valueOf(c29.split(" ")[1].trim()), "jhyhasd"));
            if (!StringUtils.isBlank(c29))
                typeInfos.set(7, new WetlandTypeInfo("sjzszsd", Double.valueOf(c30.split(" ")[0]), Double.valueOf(c30.split(" ")[1].trim()), "jhyhasd"));
            if (!StringUtils.isBlank(c30))
                typeInfos.set(8, new WetlandTypeInfo("haxxsh", Double.valueOf(c31.split(" ")[0]), Double.valueOf(c31.split(" ")[1].trim()), "jhyhasd"));
            if (!StringUtils.isBlank(c31))
                typeInfos.set(9, new WetlandTypeInfo("haxdsh", Double.valueOf(c32.split(" ")[0]), Double.valueOf(c32.split(" ")[1].trim()), "jhyhasd"));
            if (!StringUtils.isBlank(c32))
                typeInfos.set(10, new WetlandTypeInfo("cxssc", Double.valueOf(c33.split(" ")[0]), Double.valueOf(c33.split(" ")[1].trim()), "jhyhasd"));
            if (!StringUtils.isBlank(c33))
                typeInfos.set(11, new WetlandTypeInfo("cjyszz", Double.valueOf(c34.split(" ")[0]), Double.valueOf(c34.split(" ")[1].trim()), "jhyhasd"));

            if (!StringUtils.isBlank(c34))
                typeInfos.set(12, new WetlandTypeInfo("yjxhl", Double.valueOf(c35.split(" ")[0]), Double.valueOf(c35.split(" ")[1].trim()), "hlsd"));
            if (!StringUtils.isBlank(c35))
                typeInfos.set(13, new WetlandTypeInfo("jjxhjxxhl", Double.valueOf(c36.split(" ")[0]), Double.valueOf(c36.split(" ")[1].trim()), "hlsd"));
            if (!StringUtils.isBlank(c36))
                typeInfos.set(14, new WetlandTypeInfo("hfpysd", Double.valueOf(c37.split(" ")[0]), Double.valueOf(c37.split(" ")[1].trim()), "hlsd"));
            if (!StringUtils.isBlank(c37))
                typeInfos.set(15, new WetlandTypeInfo("kstrdsd", Double.valueOf(c38.split(" ")[0]), Double.valueOf(c38.split(" ")[1].trim()), "hlsd"));
            if (!StringUtils.isBlank(c38))
                typeInfos.set(16, new WetlandTypeInfo("yjxdsh", Double.valueOf(c39.split(" ")[0]), Double.valueOf(c39.split(" ")[1].trim()), "hpsd"));
            if (!StringUtils.isBlank(c38))
                typeInfos.set(17, new WetlandTypeInfo("yjxxsh", Double.valueOf(c40.split(" ")[0]), Double.valueOf(c40.split(" ")[1].trim()), "hpsd"));
            if (!StringUtils.isBlank(c40))
                typeInfos.set(18, new WetlandTypeInfo("jjxdsh", Double.valueOf(c41.split(" ")[0]), Double.valueOf(c41.split(" ")[1].trim()), "hpsd"));
            if (!StringUtils.isBlank(c41))
                typeInfos.set(19, new WetlandTypeInfo("jjxxsh", Double.valueOf(c42.split(" ")[0]), Double.valueOf(c42.split(" ")[1].trim()), "hpsd"));

            if (!StringUtils.isBlank(c42))
                typeInfos.set(20, new WetlandTypeInfo("xlzz", Double.valueOf(c43.split(" ")[0]), Double.valueOf(c43.split(" ")[1].trim()), "zzsd"));
            if (!StringUtils.isBlank(c43))
                typeInfos.set(21, new WetlandTypeInfo("cbzz", Double.valueOf(c44.split(" ")[0]), Double.valueOf(c44.split(" ")[1].trim()), "zzsd"));
            if (!StringUtils.isBlank(c44))
                typeInfos.set(22, new WetlandTypeInfo("gczz", Double.valueOf(c45.split(" ")[0]), Double.valueOf(c45.split(" ")[1].trim()), "zzsd"));
            if (!StringUtils.isBlank(c45))
                typeInfos.set(23, new WetlandTypeInfo("slzz", Double.valueOf(c46.split(" ")[0]), Double.valueOf(c46.split(" ")[1].trim()), "zzsd"));
            if (!StringUtils.isBlank(c46))
                typeInfos.set(24, new WetlandTypeInfo("nlyz", Double.valueOf(c47.split(" ")[0]), Double.valueOf(c47.split(" ")[1].trim()), "zzsd"));
            if (!StringUtils.isBlank(c47))
                typeInfos.set(25, new WetlandTypeInfo("jjxxszz", Double.valueOf(c48.split(" ")[0]), Double.valueOf(c48.split(" ")[1].trim()), "zzsd"));
            if (!StringUtils.isBlank(c48))
                typeInfos.set(26, new WetlandTypeInfo("zzhcd", Double.valueOf(c49.split(" ")[0]), Double.valueOf(c49.split(" ")[1].trim()), "zzsd"));
            if (!StringUtils.isBlank(c49))
                typeInfos.set(27, new WetlandTypeInfo("drsd", Double.valueOf(c50.split(" ")[0]), Double.valueOf(c50.split(" ")[1].trim()), "zzsd"));
            if (!StringUtils.isBlank(c50))
                typeInfos.set(28, new WetlandTypeInfo("dsqlzsd", Double.valueOf(c51.split(" ")[0]), Double.valueOf(c51.split(" ")[1].trim()), "zzsd"));

            if (!StringUtils.isBlank(c51))
                typeInfos.set(29, new WetlandTypeInfo("kt", Double.valueOf(c52.split(" ")[0]), Double.valueOf(c52.split(" ")[1].trim()), "rgsd"));
            if (!StringUtils.isBlank(c52))
                typeInfos.set(30, new WetlandTypeInfo("yhssh", Double.valueOf(c53.split(" ")[0]), Double.valueOf(c53.split(" ")[1].trim()), "rgsd"));
            if (!StringUtils.isBlank(c53))
                typeInfos.set(31, new WetlandTypeInfo("scyzc", Double.valueOf(c54.split(" ")[0]), Double.valueOf(c54.split(" ")[1].trim()), "rgsd"));
            if (!StringUtils.isBlank(c54))
                typeInfos.set(32, new WetlandTypeInfo("dtdst", Double.valueOf(c55.split(" ")[0]), Double.valueOf(c55.split(" ")[1].trim()), "rgsd"));
            if (!StringUtils.isBlank(c55))
                typeInfos.set(33, new WetlandTypeInfo("yt", Double.valueOf(c56.split(" ")[0]), Double.valueOf(c56.split(" ")[1].trim()), "rgsd"));

            wbe.setWlin_type_infos(typeInfos);
            //wbe.setId(c1);
            wbe.setWlin_type(c56);
            wetlandBasicEntityList.add(wbe);


        }
        log.info("准备更新或者添加的保护主体列表:{}", wetlandBasicEntityList);
        return wetlandBasicEntityList;
    }


    public static void checkFile(MultipartFile file) throws IOException {
        //判断文件是否存在
        if (null == file) {
            log.error("上传文件不存在！");
            throw new FileNotFoundException("上传文件不存在！");
        }
        //获得文件名
        String fileName = file.getOriginalFilename();
        //判断文件是否是excel文件
        if (!fileName.endsWith(XLS) && !fileName.endsWith(XLSX)) {
            log.error(fileName + "不是excel文件");
            throw new IOException(fileName + "不是excel文件");
        }
    }


    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        //把数字当成String来读，避免出现1读成1.0的情况
        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
            cell.setCellType(CellType.STRING);
        }
        //判断数据的类型
        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
            cellValue = String.valueOf(cell.getNumericCellValue()).trim();
        } else if (cell.getCellTypeEnum() == CellType.STRING) {
            cellValue = String.valueOf(cell.getStringCellValue()).trim();
        } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
            cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
        } else if (cell.getCellTypeEnum() == CellType.FORMULA) {
            cellValue = String.valueOf(cell.getCellFormula()).trim();
        }
        //else if (cell.getCellTypeEnum() == CellType.BLANK) {
        //    cellValue = "";
        //} else if (cell.getCellTypeEnum() == CellType.ERROR) {
        //    cellValue = "非法字符";
        //}
        else {
            cellValue = "";
        }
        return cellValue;
    }


    public static Workbook getWorkBook(MultipartFile file) throws IOException {
        //获得文件名
        //File file1 = new File("E:\\Nutstore\\新的数据表\\保护主体导入.xls");

        String fileName = file.getName();
        System.out.println(fileName);
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;

        //获取excel文件的io流

        InputStream is = file.getInputStream();
        //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象

        //if (fileName.endsWith(XLS)) {
            //2003
            //workbook = new HSSFWorkbook(is);
        //} else if (fileName.endsWith(XLSX)) {
            //2007
            //workbook = new XSSFWorkbook(is);
        //}
        if(workbook==null){
            throw new ExcelUploadException("工作簿为空！");
        }
        return workbook;
    }

}
